﻿Imports Mysoft.Map.Application.Security
Imports Mysoft.Map.Data
Imports System.Data

Partial Public Class Desk_Yqyw
    Inherits Mysoft.Map.Application.Security.AppPage

    Protected strWrgcount, strWqycount, strJdjcount, strJfkcount, strWxdcount, strWajcount As String

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim strProjGUID As String
        '初始值为0
        strWrgcount = "0"
        strWqycount = "0"
        strJdjcount = "0"
        strJfkcount = "0"
        strWxdcount = "0"
        strWajcount = "0"
        If Not IsPostBack Then
            If Not Request.QueryString("ProjGUID") Is Nothing Then
                strProjGUID = Request.QueryString("ProjGUID")
                Bind_Data(strProjGUID)
            End If

        End If

    End Sub

    Private Sub Bind_Data(ByVal strProjGUID As String)
        'Dim strCusFilter_Wrg, strCusFilter_Wqy, strCusFilter_Jdj, strCusFilter_Jfk, strCusFilter_Wxd, strCusFilter_Waj As String
        Dim dt As New DataTable
        Dim strAlterDays As String
        Dim strSQL As String
        'Dim myPubMng As New Mysoft.Slxt.Business.PublicMng

        '逾期未认购统计
        'strSQL = "Select count(8) From vs_Yqywcb_Wrg where ProjGUID in ('" & strProjGUID.Replace(",", "','") & "') and " & strCusFilter & ""
        '加用户权限过滤
        Dim strCusFilter_Wrg, strCusFilter_Wrg2 As String
        strCusFilter_Wrg = Mysoft.Slxt.Business.PublicMng.ZYHAuthorize(Session("UserGUID").ToString, "s_BookingOppRight", "01010212")
        strCusFilter_Wrg2 = Mysoft.Slxt.Business.PublicMng.ZYHAuthorize(Session("UserGUID").ToString, "s_OrderOppRight", "01010212")

        strSQL = "SELECT SUM(NUM) FROM ( " & _
                    " SELECT COUNT(1) AS NUM FROM s_Booking Book LEFT JOIN s_Opportunity Opp ON Opp.OppGUID = Book.OppGUID " & _
                    " WHERE(Book.EndDate Is Not NULL) " & _
                    " AND DATEDIFF(dd,book.EndDate,GETDATE())>0 " & _
                    " AND Book.Status='排号' " & _
                    " AND Book.ProjGUID in ('" & strProjGUID.Replace(",", "','") & "') and " & strCusFilter_Wrg & " " & _
                    " UNION ALL " & _
                    " SELECT COUNT(1) AS NUM FROM s_Order O " & _
                    " WHERE(O.EndDate Is Not NULL) " & _
                    " AND DATEDIFF(dd,O.EndDate,GETDATE())>0 " & _
                    " AND O.Status='激活'  " & _
                    " AND (O.OrderType='小订' OR O.OrderType='预留')  " & _
                    " AND O.ProjGUID in ('" & strProjGUID.Replace(",", "','") & "') and " & strCusFilter_Wrg2 & " " & _
                    " ) Yqywcb_Wrg "

        strWrgcount = MyDB.GetDataItemString(strSQL)
        txtWrgcount.Text = strWrgcount


        '逾期未签约统计
        'strSQL = "Select count(8) From vs_Yqywcb_Wqy where ProjGUID in ('" & strProjGUID.Replace(",", "','") & "') and " & strCusFilter & ""
        Dim strCusFilter_Wqy As String
        strCusFilter_Wqy = Mysoft.Slxt.Business.PublicMng.ZYHAuthorize(Session("UserGUID").ToString, "s_OrderOppRight", "01010212")

        strSQL = "SELECT COUNT(1) AS NUM FROM s_Order O" & _
                    " WHERE(O.EndDate Is Not NULL) " & _
                    " AND DATEDIFF(dd,O.EndDate,GETDATE())>0  " & _
                    " AND O.Status='激活' " & _
                    " AND O.OrderType='认购'   " & _
                    " AND O.ProjGUID in ('" & strProjGUID.Replace(",", "','") & "') and " & strCusFilter_Wqy & " "

        strWqycount = MyDB.GetDataItemString(strSQL)
        txtWqycount.Text = strWqycount


        '逾期未缴或未缴清定金统计
        'strSQL = "Select count(8) From vs_Yqywcb_Jdj where ProjGUID in ('" & strProjGUID.Replace(",", "','") & "') and " & strCusFilter & ""
        Dim strCusFilter_Jdj, strCusFilter_Jdj2 As String
        strCusFilter_Jdj = Mysoft.Slxt.Business.PublicMng.ZYHAuthorize(Session("UserGUID").ToString, "s_OrderOppRight", "01010212")
        strCusFilter_Jdj2 = Mysoft.Slxt.Business.PublicMng.ZYHAuthorize(Session("UserGUID").ToString, "s_ContractOppRight", "01010212")

        strSQL = "SELECT SUM(NUM) FROM (" & _
                    " SELECT COUNT(1) AS NUM FROM s_Order O INNER JOIN " & _
                    " (select sum(RmbYe) as QKTotal,TradeGUID,min(s_fee.LastDate) AS YQdate from s_Fee where  RmbYe >0 AND lastDate is not null and DATEDIFF(dd,lastDate,GETDATE())>0 and ItemType='非贷款类房款' AND ItemName='定金' group by s_fee.TradeGUID having sum(RmbYe)>0) sf ON sf.TradeGUID=O.TradeGUID " & _
                    " WHERE o.Status='激活' AND ( o.OrderType='小订' OR o.OrderType='认购' ) " & _
                    " AND O.ProjGUID in ('" & strProjGUID.Replace(",", "','") & "') and " & strCusFilter_Jdj & " " & _
                    " UNION ALL  " & _
                    " SELECT COUNT(1) AS NUM FROM s_Contract C INNER JOIN " & _
                    " (select sum(RmbYe) as QKTotal,TradeGUID,min(s_fee.LastDate) AS YQdate from s_Fee where  RmbYe >0 AND lastDate is not null and DATEDIFF(dd,lastDate,GETDATE())>0 and ItemType='非贷款类房款' AND ItemName='定金' group by s_fee.TradeGUID  having sum(RmbYe)>0) sf ON sf.TradeGUID=c.TradeGUID " & _
                    " WHERE C.Status='激活'" & _
                    " AND C.ProjGUID in ('" & strProjGUID.Replace(",", "','") & "') and " & strCusFilter_Jdj2 & " " & _
                    " ) Yqywcb_Jdj "

        strJdjcount = MyDB.GetDataItemString(strSQL)
        txtJdjcount.Text = strJdjcount

        '逾期未缴或未缴清房款(含税费)统计
        'strSQL = "Select count(8) From vs_Yqywcb_Jfk where ProjGUID in ('" & strProjGUID.Replace(",", "','") & "') and " & strCusFilter & ""
        Dim strCusFilter_Jfk, strCusFilter_Jfk2 As String
        strCusFilter_Jfk = Mysoft.Slxt.Business.PublicMng.ZYHAuthorize(Session("UserGUID").ToString, "s_OrderOppRight", "01010212")
        strCusFilter_Jfk2 = Mysoft.Slxt.Business.PublicMng.ZYHAuthorize(Session("UserGUID"), "s_ContractOppRight", "01010212")

        strSQL = "SELECT SUM(NUM) FROM ( " & _
                    " SELECT COUNT(1) AS NUM FROM s_Order O INNER JOIN " & _
                    " (SELECT SUM(RmbYe) AS QKTotal,TradeGUID,min(s_fee.LastDate) AS YQdate from s_Fee where  RmbYe >0 AND lastDate IS NOT NULL and DATEDIFF(dd,lastDate,GETDATE())>0 and ((ItemType='非贷款类房款' AND (ItemName='首期' OR ItemName='楼款')) or ItemType='代收费用') group by s_fee.TradeGUID having sum(RmbYe)>0) sf on sf.TradeGUID=O.TradeGUID " & _
                    " WHERE o.Status='激活' AND (o.OrderType='小订' OR o.OrderType='认购') " & _
                    " AND o.ProjGUID in ('" & strProjGUID.Replace(",", "','") & "') and " & strCusFilter_Jfk & " " & _
                    " UNION ALL  " & _
                    " SELECT COUNT(1) AS NUM FROM s_Contract C INNER JOIN " & _
                    " (SELECT SUM(RmbYe) AS QKTotal,TradeGUID,min(s_fee.LastDate) AS YQdate from s_Fee where  RmbYe >0 AND lastDate IS NOT NULL and DATEDIFF(dd,lastDate,GETDATE())>0 and ((ItemType='非贷款类房款' AND (ItemName='首期' OR ItemName='楼款')) or ItemType='代收费用') group by s_fee.TradeGUID having sum(RmbYe)>0) sf on sf.TradeGUID=C.TradeGUID " & _
                    " WHERE C.Status='激活' " & _
                    " AND C.ProjGUID in ('" & strProjGUID.Replace(",", "','") & "') and " & strCusFilter_Jfk2 & " " & _
                    " ) vs_Yqywcb_Jfk"
        strJfkcount = MyDB.GetDataItemString(strSQL)
        txtJfkcount.Text = strJfkcount


        '逾期银行未下贷统计
        'strSQL = "Select count(8) From vs_Yqywcb_Wxd where ProjGUID in ('" & strProjGUID.Replace(",", "','") & "') and " & strCusFilter & ""
        Dim strCusFilter_Wxd As String
        strCusFilter_Wxd = Mysoft.Slxt.Business.PublicMng.ZYHAuthorize(Session("UserGUID").ToString, "s_ContractOppRight", "01010212")

        strSQL = "SELECT COUNT(1) AS NUM FROM s_Contract C INNER JOIN  " & _
                    " (select sum(RmbYe) AS QKTotal,TradeGUID,min(s_fee.LastDate) AS YQdate from s_Fee where  RmbYe > 0 AND lastDate is not null AND DATEDIFF(dd,lastDate,GETDATE())>0 AND ItemType='贷款类房款'AND (ItemName='银行按揭' OR ItemName='公积金') GROUP BY s_fee.TradeGUID HAVING sum(RmbYe)>0 ) sf ON sf.TradeGUID=C.TradeGUID  " & _
                    " WHERE C.Status='激活' " & _
                    " AND (C.AjTotal>0 OR C.GjjTotal>0) " & _
                    " AND C.ProjGUID in ('" & strProjGUID.Replace(",", "','") & "') and " & strCusFilter_Wxd & ""

        strWxdcount = MyDB.GetDataItemString(strSQL)
        txtWxdcount.Text = strWxdcount


        '逾期未办按揭统计
        'strSQL = "Select count(8) From vs_Yqywcb_Waj where ProjGUID in ('" & strProjGUID.Replace(",", "','") & "') and " & strCusFilter & ""
        Dim strCusFilter_Waj As String
        strCusFilter_Waj = Mysoft.Slxt.Business.PublicMng.ZYHAuthorize(Session("UserGUID").ToString, "s_ContractOppRight", "01010212", "C")

        strSQL = "SELECT COUNT(1) AS NUM FROM s_Contract C INNER JOIN " & _
                    "(select min(EndDate) as YQDate,ContractGUID from s_SaleService where EndDate is not null and ServiceProc='未办理' and (ServiceItem='按揭贷款' or ServiceItem='公积金贷款') and DATEDIFF(dd,EndDate,GETDATE())>0  group by s_SaleService.ContractGUID ) ss on ss.ContractGUID = C.ContractGUID " & _
                    "WHERE C.STATUS='激活' " & _
                    "AND C.ProjGUID in ('" & strProjGUID.Replace(",", "','") & "') and " & strCusFilter_Waj & ""
        strWajcount = MyDB.GetDataItemString(strSQL)
        txtWajcount.Text = strWajcount
    End Sub

End Class